<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Product_model extends CI_Model{

	function __construct()
    {
        // Call the Model constructor
        parent::__construct();
    }
	function loadroom($id)
	{
		$this->load->database();
		if($id> 0)
		{
			$QueryString="SELECT tblroom.*,tblproduct.productname FROM tblroom LEFT JOIN tblproduct ON tblroom.productid=tblproduct.productid WHERE id=" . $id;
			$query = $this->db->query($QueryString);
		}else{
			return array();
		}
		if ($query->num_rows() > 0)
        {
          return $query->result();
        } 
		else 
		{
          return array();
        }
	}
	function loadproduct($productid)
	{
		$this->load->database();
		if($productid > 0)
		{
			$QueryString="SELECT tblproduct.*,tblcate.catename FROM tblproduct LEFT JOIN tblcate ON tblcate.cateid=tblproduct.cateid WHERE productid=" . $productid;
			$query = $this->db->query($QueryString);
		}else{
			return array();
		}
		if ($query->num_rows() > 0)
        {
          return $query->result();
        } 
		else 
		{
          return array();
        }
	}
	function loadcateinfo($cateid)
	{
		$this->load->database();
		if($cateid > 0)
		{
			$QueryString="SELECT tblcate.* FROM tblcate WHERE cateid=" . $cateid;
			$query = $this->db->query($QueryString);
		}else{
			return array();
		}
		if ($query->num_rows() > 0)
        {
          return $query->result();
        } 
		else 
		{
          return array();
        }
	}
	function loadbanner($bannerid)
	{
		$this->load->database();
		if($bannerid > 0)
		{
			$QueryString="SELECT tblbanner.*,CASE WHEN isbanner=1 THEN 'Banner' WHEN isbanner=2 THEN 'Quảng cáo' ELSE 'Tin nóng' END AS position FROM tblbanner WHERE id=" . $bannerid;
			$query = $this->db->query($QueryString);
		}else{
			return array();
		}
		if ($query->num_rows() > 0)
        {
          return $query->result();
        } 
		else 
		{
          return array();
        }
	}
	function loadpropertiespage($productid,$index)
	{
		$this->load->database();
		$QueryString="SELECT tp.*,p.productname FROM tblproperties tp INNER JOIN tblproduct as p ON p.productid=tp.productid WHERE p.productid=" . $productid ." LIMIT ".(($index-1) * 10).",10";
		$query = $this->db->query($QueryString);
		if ($query->num_rows() > 0)
        {
          return $query->result();
        } 
		else 
		{
          return array();
        }
	}
	function loadroompage($productid,$index)
	{
		$this->load->database();
		$QueryString="SELECT tblroom.*,tblproduct.productname FROM tblroom LEFT JOIN tblproduct ON tblroom.productid=tblproduct.productid WHERE COALESCE(parentid,0)=" . $productid ." LIMIT ".(($index-1) * 10).",10";
		$query = $this->db->query($QueryString);
		if ($query->num_rows() > 0)
        {
          return $query->result();
        } 
		else 
		{
          return array();
        }
	}
	function loadbannerpage($index)
	{
		$this->load->database();
		$QueryString="SELECT p.*,CASE WHEN isbanner=1 THEN 'Banner' WHEN isbanner=2 THEN 'Quảng cáo' ELSE 'Tin nóng' END AS position  FROM tblbanner AS p ORDER BY createdate DESC LIMIT ".(($index-1) * 10).",10";
		$query = $this->db->query($QueryString);
		if ($query->num_rows() > 0)
        {
          return $query->result();
        } 
		else 
		{
          return array();
        }
	}
	function loadproducpage($index)
	{
		if(!($index>1)){
			$index=1;
		}
		$this->load->database();
		$QueryString="SELECT tblproduct.*,tblcate.catename FROM tblproduct LEFT JOIN tblcate ON tblcate.cateid=tblproduct.cateid LIMIT ".(($index-1) * 10).",10";
		$query = $this->db->query($QueryString);
	
		if ($query->num_rows() > 0)
        {
          return $query->result();
        } 
		else 
		{
          return array();
        }
	}
	function loadcatepage($index)
	{
		if(!($index>1)){
			$index=1;
		}
		$this->load->database();
		$QueryString="SELECT tblcate.* FROM tblcate LIMIT ".(($index-1) * 10).",".(($index-1) * 10 + 10);
		$query = $this->db->query($QueryString);
	
		if ($query->num_rows() > 0)
        {
          return $query->result();
        } 
		else 
		{
          return array();
        }
	}
	
	function loadcate($cateid)
	{
		$this->load->database();
		if($cateid > 0)
		{
			$QueryString="SELECT * FROM tblcate WHERE cateid1 in(SELECT cateid1 FROM tblcate WHERE cateid=" . $cateid .") AND COALESCE(cateid2,0) != 0";
		}else{
			$QueryString="SELECT * FROM tblcate WHERE COALESCE(cateid2,0) = 0";
		}
		$query = $this->db->query($QueryString);
		if ($query->num_rows() > 0)
        {
          return $query->result();
        } 
		else 
		{
          return array();
        }
	}
	function loadproductotal()
	{
		$this->load->database();
		$QueryString="SELECT COUNT(1) AS Totalrow FROM tblproduct";
		$query = $this->db->query($QueryString);
		if ($query->num_rows() > 0)
        {
          foreach($query->result() as $row)
          {
          	return $row->Totalrow;
          }
        } 
        return 0;
	}
	function loadroomtotal($parentid)
	{
		$this->load->database();
		$QueryString="SELECT COUNT(1) AS Totalrow FROM tblroom WHERE COALESCE(parentid,0)=".$parentid;
		$query = $this->db->query($QueryString);
		if ($query->num_rows() > 0)
        {
          foreach($query->result() as $row)
          {
          	return $row->Totalrow;
          }
        } 
        return 0;
	}
	function loadbannertotal()
	{
		$this->load->database();
		$QueryString="SELECT COUNT(1) AS Totalrow FROM tblbanner";
		$query = $this->db->query($QueryString);
		if ($query->num_rows() > 0)
        {
          foreach($query->result() as $row)
          {
          	return $row->Totalrow;
          }
        } 
        return 0;
	}
	function loadpropertiestotal($productid)
	{
		$this->load->database();
		$QueryString="SELECT COUNT(1) AS Totalrow FROM tblproperties WHERE productid=".$productid;
		$query = $this->db->query($QueryString);
		if ($query->num_rows() > 0)
        {
          foreach($query->result() as $row)
          {
          	return $row->Totalrow;
          }
        } 
        return 0;
	}
	function loadcatetotal()
	{
		$this->load->database();
		$QueryString="SELECT COUNT(1) AS Totalrow FROM tblcate";
		$query = $this->db->query($QueryString);
		if ($query->num_rows() > 0)
        {
          foreach($query->result() as $row)
          {
          	return $row->Totalrow;
          }
        } 
        return 0;
	}
	function loadcateparent($cateid)
	{
		$this->load->database();
		if($cateid > 0)
		{
			$QueryString="SELECT * FROM tblcate WHERE cateid1 in(SELECT cateid1 FROM tblcate WHERE cateid=" . $cateid .") AND COALESCE(cateid2,0) = 0";
		}else{
			$QueryString="SELECT * FROM tblcate WHERE COALESCE(cateid2,0) = 0";
		}
		$query = $this->db->query($QueryString);
		if ($query->num_rows() > 0)
        {
          foreach($query->result() as $row)
          {
          	return $row->cateid;
          }
        } 
        return 0;
	}
	
	function loadproperties($propertiesid)
	{
		$this->load->database();
		if($propertiesid > 0)
		{
			$QueryString="SELECT * FROM tblproperties WHERE propertiesid=" . $propertiesid;
			$query = $this->db->query($QueryString);
		}else{
			return array();
		}
		if ($query->num_rows() > 0)
        {
          return $query->result();
        } 
		else 
		{
          return array();
        }
	}
	
	function updateproduct($productid,$cateid,$productname,$catename,$descr,$content,$imgpath,$url,$ishot,$price,$active,$view,$email,$phone,$unit)
	{
		$this->load->database();
		$catename = $this->getcatename($cateid);
		$data = array(
		   'cateid' => $cateid ,
		   'catename' => $catename ,
		   'productname' => $productname,
		   'description' => $descr,
		   'content' => $content,
		   'imgpath' => $imgpath,
		   'url' => $url,
		   'ishot' => $ishot,
		   'price' => $price,
		   'active' => $active,
		   'view' => $view,
		   'email' => $email,
		   'phone' => $phone,
		   'unit' => $unit,
		   'searchcontent' => preg_replace("/\t/","",preg_replace("/\s/","",$productname.$this->vn_str_filter($productname).$catename.$this->vn_str_filter($catename).$price.$unit.$descr.$this->vn_str_filter($descr)))
		);
		if($productid > 0)
		{	
			$data['updatedate'] = date('Y').'-'.date('m').'-'.date('d');
			$this->db->where('productid', $productid);
			$this->db->update('tblproduct', $data); 
		}
		else
		{
			$data['createdate'] = date('Y').'-'.date('m').'-'.date('d');
			$this->db->insert('tblproduct', $data); 
		}
	}
	function getcatename($cateid)
	{
		$this->load->database();
		$QueryString="SELECT * FROM tblcate WHERE active =1 AND cateid = ".$cateid;
		$query = $this->db->query($QueryString);
		if ($query->num_rows() > 0)
        {
          foreach($query->result() as $row)
          {
          	return $row->catename;
          }
        }
		return "";
	}
	function updatecate($id,$cateid,$catename,$imgpath,$url,$ishome,$active)
	{
		$this->load->database();
		$QueryString="SELECT cateid1 FROM tblcate WHERE cateid=" . $cateid;
		$query = $this->db->query($QueryString);
		$cateid1=0;
		if ($query->num_rows() > 0)
        {
          foreach($query->result() as $row){
          	$cateid1=$row->cateid1;
          }
        }
        if($cateid1)
        if($id > 0)
		{
        	$QueryString="SELECT cateid2 FROM tblcate WHERE cateid=" . $id;	
		}
		else
		{
        	$QueryString="SELECT (MAX(cateid2) + 1) AS cateid2 FROM tblcate ";
		}	
		$cateid2=0;
		$query = $this->db->query($QueryString);
        if ($query->num_rows() > 0)
        {
          foreach($query->result() as $row){
          	$cateid2=$row->cateid2;
          }
        } 
        if($cateid==0){
        	$QueryString="SELECT (COALESCE(MAX(cateid1),0) + 1) AS cateid1 FROM tblcate";
        	$query = $this->db->query($QueryString);
        	$cateid1=1;
	        if ($query->num_rows() > 0)
	        {
	          foreach($query->result() as $row){
	          	$cateid1=$row->cateid1;
	          }
	        }
        	$cateid2=0;
        }
		$data = array(
		   'cateid1' => $cateid1 ,
		   'catename' => $catename ,
		   'imgpath' => $imgpath,
		   'url' => $url,
		   'ishome' => $ishome,
		   'active' => $active,
		   'cateid2' => $cateid2
		);
		if($id > 0)
		{	
			$this->db->where('cateid', $id);
			$this->db->update('tblcate', $data); 
		}
		else
		{
			$this->db->insert('tblcate', $data); 
		}
	}
	function updateproperties($id,$productid,$propertiesname,$description)
	{
		$data = array(
		   'productid' => $productid ,
		   'propertiesname' => $propertiesname ,
		   'description' => $description
		);
		if($id > 0)
		{	
			$this->db->where('propertiesid', $id);
			$this->db->update('tblproperties', $data); 
		}
		else
		{
			$this->db->insert('tblproperties', $data); 
		}
	}
	function updatebanner($bannerid,$bannername,$cateid,$catename,$url,$imgpath,$active,$isbanner)
	{
		$data = array(
		   'bannername' => $bannername ,
		   'url' => $url,
		   'imgpath' => $imgpath,
		   'active' => $active,
		   'isbanner' => $isbanner,
		   'cateid' => $cateid,
		   'catename' => $catename
		);
		if($bannerid > 0)
		{	
			$this->db->where('id', $bannerid);
			$this->db->update('tblbanner', $data); 
		}
		else
		{
			$this->db->insert('tblbanner', $data); 
		}
	}
	function updateroom($id,$parentid,$roomname,$descr,$content,$imgpath,$view,$ishot,$active,$view)
	{
		$data = array(
		   'roomname' => $roomname ,
		   'description' => $descr,
		   'content' => $content,
		   'parentid' => $parentid,
		   'active' => $active,
		   'ishot' => $ishot,
		   'imgpath' => $imgpath,
		   'view' => $view,
		   'searchcontent' => preg_replace("/\t/","",preg_replace("/\s/","",$roomname.$this->vn_str_filter($roomname).$descr.$this->vn_str_filter($descr)))
		);
		if($id > 0)
		{	
			$this->db->where('id', $id);
			$this->db->update('tblroom', $data); 
		}
		else
		{
			$this->db->insert('tblroom', $data); 
		}
	}
	function deletebanner($bannerid)
	{
		$this->load->database();
		$this->db->where('id', $bannerid);
		$this->db->delete('tblbanner'); 
	}
	function delete($productid)
	{
		$this->load->database();
		$this->db->where('productid', $productid);
		$this->db->delete('tblproduct'); 
	}
	function deletecate($cateid)
	{
		$this->load->database();
		$this->db->where('cateid', $cateid);
		$this->db->delete('tblcate'); 
	}
	function deleteproperties($id)
	{
		$this->load->database();
		$this->db->where('propertiesid', $id);
		$this->db->delete('tblproperties'); 
	}
	function deleteroom($id)
	{
		$this->load->database();
		$this->db->where('id', $id);
		$this->db->delete('tblroom'); 
	}
	
	function vn_str_filter ($str){
        $unicode = array(
            'a'=>'á|à|ả|ã|ạ|ă|ắ|ặ|ằ|ẳ|ẵ|â|ấ|ầ|ẩ|ẫ|ậ',
            'd'=>'đ',
            'e'=>'é|è|ẻ|ẽ|ẹ|ê|ế|ề|ể|ễ|ệ',
            'i'=>'í|ì|ỉ|ĩ|ị',
            'o'=>'ó|ò|ỏ|õ|ọ|ô|ố|ồ|ổ|ỗ|ộ|ơ|ớ|ờ|ở|ỡ|ợ',
            'u'=>'ú|ù|ủ|ũ|ụ|ư|ứ|ừ|ử|ữ|ự',
            'y'=>'ý|ỳ|ỷ|ỹ|ỵ',
			'A'=>'Á|À|Ả|Ã|Ạ|Ă|Ắ|Ặ|Ằ|Ẳ|Ẵ|Â|Ấ|Ầ|Ẩ|Ẫ|Ậ',
            'D'=>'Đ',
            'E'=>'É|È|Ẻ|Ẽ|Ẹ|Ê|Ế|Ề|Ể|Ễ|Ệ',
            'I'=>'Í|Ì|Ỉ|Ĩ|Ị',
            'O'=>'Ó|Ò|Ỏ|Õ|Ọ|Ô|Ố|Ồ|Ổ|Ỗ|Ộ|Ơ|Ớ|Ờ|Ở|Ỡ|Ợ',
            'U'=>'Ú|Ù|Ủ|Ũ|Ụ|Ư|Ứ|Ừ|Ử|Ữ|Ự',
            'Y'=>'Ý|Ỳ|Ỷ|Ỹ|Ỵ',
        );
       foreach($unicode as $nonUnicode=>$uni){
            $str = preg_replace("/($uni)/i", $nonUnicode, $str);
       }
		return $str;
    }
    ///////// report //////////////////////
    function getreportview()
    {
    	$this->load->database();
		$QueryString="select catename,date(updatedate) as updatedate,count(1)  AS Numbder from tblproduct where year(updatedate)=year(now()) and month(updatedate)=month(now()) group by catename,date(updatedate);";
		$query = $this->db->query($QueryString);
		
		if ($query->num_rows() > 0)
        {
          return $query->result();
        } 
		else 
		{
          return array();
        }
    }
    
 	function getreportinput()
    {
    	$this->load->database();
		$QueryString="select catename,date(createdate) as createdate,count(1)  AS Numbder from tblproduct where year(createdate)=year(now()) and month(createdate)=month(now()) group by catename,date(createdate);";
		
		$query = $this->db->query($QueryString);
		
		if ($query->num_rows() > 0)
        {
          return $query->result();
        } 
		else 
		{
          return array();
        }
    }
}
?>